import pymysql
#定义数据库工具类
class DBTools:
    #建立连接
    @staticmethod
    def __create_conn():
        #指定host，port，user，password，database，charset，返回连接
        conn = pymysql.connect(host="211.103.136.244", port=7061, user="student", password="iHRM_student_2022",database="ihrm", charset="utf8")
        return conn
    #查询方法
    @staticmethod
    def select_one(sql):
        conn=None
        cursor=None
        res=None
        # 获取连接，创建游标，执行sql，提取结果集，返回一条结果
        try:
            conn = DBTools.__create_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            res = cursor.fetchone()
        except Exception as e:
            print("查询一条数据:",e)
        finally:
            cursor.close()
            conn.close()
            return res

    @staticmethod
    def select_many(sql,n):
        # 获取连接，创建游标，执行sql，提取结果集，返回多条结果
        conn = None
        cursor = None
        res = None
        # 获取连接，创建游标，执行sql，提取结果集，返回一条结果
        try:
            conn = DBTools.__create_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            res = cursor.fetchmany(n)
        except Exception as e:
            print("查询多条数据:", e)
        finally:
            cursor.close()
            conn.close()
            return res
    @staticmethod
    def select_all(sql):
        # 获取连接，创建游标，执行sql，提取结果集，返回全部结果
        # 获取连接，创建游标，执行sql，提取结果集，返回多条结果
        conn = None
        cursor = None
        res = None
        # 获取连接，创建游标，执行sql，提取结果集，返回一条结果
        try:
            conn = DBTools.__create_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            res = cursor.fetchall()
        except Exception as e:
            print("查询全部数据:", e)
        finally:
            cursor.close()
            conn.close()
            return res
    #增删改方法
    @staticmethod
    def db_idu(sql):
        conn=None
        cursor=None
        # 获取连接，创建游标，执行sql，提交/回滚事务
        try:
            conn = DBTools.__create_conn()
            cursor = conn.cursor()
            ret=cursor.execute(sql)
            print("Affected rows:",ret)
            conn.commit()
        except Exception as e:
            conn.rollback()
            print("增删改失败:",e)
        finally:
            conn.close()
            cursor.close()